package com.huatai.bi.service.impl;

import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.huatai.bi.dto.*;
import com.huatai.bi.entity.*;
import com.huatai.bi.mapper.DataProcessInfoMapper;
import com.huatai.bi.service.*;
import com.huatai.bi.utils.Constants;
import com.huatai.bi.utils.createTableUtils;
import com.huatai.bi.vo.DataByDtIdVo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.*;
import java.util.stream.Collectors;

/**
 * @author : zhangbenting
 * @date : 2024/6/27 20:51
 * @Version: 1.0
 */
@Service
public class GroupCollectMergeServiceImpl implements GroupCollectMergeService {

    @Autowired
    private DataProcessService dataProcessService;
    @Autowired
    private DataProcessInfoService dataProcessInfoService;
    @Autowired
    private DirectDataSetService directDataSetService;
    @Autowired
    private DataProcessInfoMapper dataProcessInfoMapper;
    @Autowired
    private ClickHouseJDBCService clickHouseJDBCService;
    @Autowired
    private DataSetColumnService dataSetColumnService;
    @Autowired
    private DirectoryService directoryService;

    @Override
    public Object getGroupCollectMergePreview(GroupCollectMergeDto groupCollectMergeDto) {
        String sql = getSql(groupCollectMergeDto);
        sql = sql + " LIMIT 0,50";
        JSONObject object = clickHouseJDBCService.queryTableBySqlToSuccess(sql);
        return object;
    }

    @Override
    public boolean formulaWhetherCompliance(GroupCollectMergeDto groupCollectMergeDto) {
        String sql = getSql(groupCollectMergeDto);
        JSONObject object = clickHouseJDBCService.queryTableBySqlToSuccess(sql);
        if (object.getString("success").equals("true")) {
            return true;
        }
        return false;
    }

    public String getSql(GroupCollectMergeDto groupCollectMergeDto) {
        LambdaQueryWrapper<DataProcessInfoEntity> wrapper = Wrappers.lambdaQuery();
        wrapper.eq(DataProcessInfoEntity::getDpId, groupCollectMergeDto.getDpId());
        List<DataProcessInfoEntity> list = dataProcessInfoService.list(wrapper);
        String str = "";
        if (list.size() > 0) {
            DataProcessInfoEntity dataProcessInfoEntity = list.stream().max(Comparator.comparing(DataProcessInfoEntity::getOrderNum)).orElse(null);
            if (ObjectUtil.isNotEmpty(dataProcessInfoEntity)) {
                str = dataProcessInfoEntity.getProcessTable();
            }
        } else {
            DataProcessEntity dataProcessEntity = dataProcessService.getById(groupCollectMergeDto.getDpId());
            DirectoryEntity directoryEntity = directoryService.getById(dataProcessEntity.getDtId());
            str = directoryEntity.getTableName();
        }
        DataProcessInfoEntity processDataInfo = getProcessDataInfo(groupCollectMergeDto, str);
        return processDataInfo.getParameterValues();
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public boolean saveGroupCollectMerge(GroupCollectMergeDto groupCollectMergeDto) {
        DataProcessEntity dataProcessEntity = dataProcessService.getById(groupCollectMergeDto.getDpId());
        LambdaQueryWrapper<DataProcessInfoEntity> wrapper = Wrappers.lambdaQuery();
        wrapper.eq(DataProcessInfoEntity::getDpId, groupCollectMergeDto.getDpId());
        List<DataProcessInfoEntity> list = dataProcessInfoService.list(wrapper);
        if (list.size() > 0) {
            //加工非原始数据
            DataProcessInfoEntity dataProcessInfoEntity = list.stream().max(Comparator.comparing(DataProcessInfoEntity::getOrderNum)).orElse(null);
            if (ObjectUtil.isNotEmpty(dataProcessInfoEntity)) {
                DataProcessInfoEntity dataProcessInfo = getProcessDataInfo(groupCollectMergeDto, dataProcessInfoEntity.getProcessTable());
                dataProcessInfo.setOrderNum(dataProcessInfoEntity.getOrderNum() + 1);
                //建表
//                clickHouseJDBCService.createTableProcessDS(dataProcessInfo.getProcessTable()
//                        , dataProcessEntity.getDtId(), dataProcessInfo.getParameterValues());
                String sql = "create table " + dataProcessInfo.getProcessTable() + " " + dataProcessInfo.getParameterValues();
                dataProcessInfoMapper.getDataPreview(sql);
                //新增字段表信息
                saveProcessColumn(groupCollectMergeDto.getDpId(), dataProcessInfo.getColumnJson());
                return dataProcessInfoService.save(dataProcessInfo);
            }
        } else {
            //加工原始数据
            DirectoryEntity directoryEntity = directoryService.getById(dataProcessEntity.getDtId());
            String tableName = directoryEntity.getTableName();
            if (ObjectUtil.isNotEmpty(tableName)) {
                DataProcessInfoEntity processDataInfo = getProcessDataInfo(groupCollectMergeDto, tableName);
                processDataInfo.setOrderNum(1);
                //建表
//                clickHouseJDBCService.createTableProcessDS(processDataInfo.getProcessTable()
//                        , dataProcessEntity.getDtId(), processDataInfo.getParameterValues());
                //模拟建表
                String sql = "create table " + processDataInfo.getProcessTable() + " " + processDataInfo.getParameterValues();
                dataProcessInfoMapper.getDataPreview(sql);
                //新增字段表信息
                saveDataSetColumn(dataProcessEntity.getDtId(), groupCollectMergeDto.getDpId(), processDataInfo.getColumnJson());
                return dataProcessInfoService.save(processDataInfo);
            }
        }
        return false;
    }

    public boolean saveDataSetColumn(Long dtId, Long dpId, String dataSetColumnStr) {
        List<DataSetColumnEntity> list = dataSetColumnService.list(
                new LambdaQueryWrapper<DataSetColumnEntity>().eq(DataSetColumnEntity::getDataSetId, dtId));
        List<DataSetColumnEntity> dataSetColumn = JSON.parseArray(dataSetColumnStr, DataSetColumnEntity.class);
        list.stream().forEach(item -> {
            item.setId(null);
            item.setDataSetId(dpId);
            item.setCreateTime(null);
            item.setUpdateTime(null);
        });
        int size = list.size();
        for (DataSetColumnEntity column : dataSetColumn) {
            column.setDataSetId(dpId);
            size = size + 1;
            column.setColumnPosition(size);
            list.add(column);
        }
        return dataSetColumnService.saveBatch(list);
    }

    public boolean saveProcessColumn(Long dpId, String dataSetColumnStr) {
        List<DataSetColumnEntity> list = dataSetColumnService.list(
                new LambdaQueryWrapper<DataSetColumnEntity>().eq(DataSetColumnEntity::getDataSetId, dpId));
        List<DataSetColumnEntity> dataSetColumn = JSON.parseArray(dataSetColumnStr, DataSetColumnEntity.class);
        int size = list.size();
        for (DataSetColumnEntity column : dataSetColumn) {
            column.setDataSetId(dpId);
            size = size + 1;
            column.setColumnPosition(size);
        }
        return dataSetColumnService.saveBatch(dataSetColumn);
    }

    public DataProcessInfoEntity getProcessDataInfo(GroupCollectMergeDto groupCollectMergeDto, String tableName) {
        String type = groupCollectMergeDto.getDataProcessingName();
        DataProcessInfoEntity dataProcessInfoEntity = new DataProcessInfoEntity();
        dataProcessInfoEntity.setDpId(groupCollectMergeDto.getDpId());
        dataProcessInfoEntity.setName(groupCollectMergeDto.getDataProcessingName());
        dataProcessInfoEntity.setOriginalParameter(groupCollectMergeDto.getParameter());
        dataProcessInfoEntity.setType(Constants.PROCESS_DATA);
        List<ColumnDto> columnDtoList = new ArrayList<>();
        switch (type) {
            //分组赋值
            case Constants.GROUP_ASSIGN:

                String groupAssignParameter = groupCollectMergeDto.getParameter();
                GroupAssignParameterDto groupAssignParameterDto = JSON.parseObject(groupAssignParameter, GroupAssignParameterDto.class);
                List<DataRangeDto> dataRange = groupAssignParameterDto.getDataRange();
                for (int i = 0; i < dataRange.size(); i++) {
                    DataRangeDto dataRangeDto = dataRange.get(i);
                    if (Double.parseDouble(dataRangeDto.getMetricsValueUpperLimit()) <= Double.parseDouble(dataRangeDto.getMetricsValueLowerLimit())) {
                        throw new SecurityException("数据区间不合规");
                    }
                    if (i + 1 <= dataRange.size() - 1) {
                        DataRangeDto dataRangeDtoNext = dataRange.get(i + 1);
                        if (!(Double.parseDouble(dataRangeDto.getMetricsValueUpperLimit()) <= Double.parseDouble(dataRangeDtoNext.getMetricsValueLowerLimit()))) {
                            throw new SecurityException("数据区间不合规");
                        }
                    }
                }
                String groupAssignSql = splicingDataAssignRowSql(tableName, groupAssignParameterDto).toString();
                ColumnDto groupColumnDto = new ColumnDto();
                groupColumnDto.setColumnName(groupAssignParameterDto.getAssignRowName());
                groupColumnDto.setColumnComment(groupAssignParameterDto.getAssignRowDescription());
                groupColumnDto.setColumnType("string");
                columnDtoList.add(groupColumnDto);
                dataProcessInfoEntity.setColumnJson(JSON.toJSONString(columnDtoList));
                dataProcessInfoEntity.setParameterValues(groupAssignSql);
                dataProcessInfoEntity.setProcessTable(createTableUtils.createTableName(Constants.PROCESS_DATA, Constants.GROUP_ASSIGN_ENGLISH));
                dataProcessInfoEntity.setDescription(groupAssignParameterDto.getAssignRowDescription());
                return dataProcessInfoEntity;

            //数据汇总
            case Constants.DATA_COLLECT:

                String dataCollectParameter = groupCollectMergeDto.getParameter();
                DataCollectDto dataCollectDto = JSON.parseObject(dataCollectParameter, DataCollectDto.class);
                String dataCollectSql = splicingDataCollectSql(tableName, dataCollectDto).toString();
                ColumnDto collectColumnDto = new ColumnDto();
                collectColumnDto.setColumnName(dataCollectDto.getCollectField());
                collectColumnDto.setColumnComment(dataCollectDto.getCollectFieldDescription());
                collectColumnDto.setColumnType("double");
                columnDtoList.add(collectColumnDto);
                dataProcessInfoEntity.setColumnJson(JSON.toJSONString(columnDtoList));
                dataProcessInfoEntity.setParameterValues(dataCollectSql);
                dataProcessInfoEntity.setProcessTable(createTableUtils.createTableName(Constants.PROCESS_DATA, Constants.DATA_COLLECT_ENGLISH));
                dataProcessInfoEntity.setDescription(dataCollectDto.getCollectFieldDescription());
                return dataProcessInfoEntity;

            //数据集合并
            case Constants.DATA_SET_UNION:
                String unionTableName = null;
                Integer orderNum = null;
                String dataSetUnionParameter = groupCollectMergeDto.getParameter();
                DataSetUnionDto dataSetUnionDto = JSON.parseObject(dataSetUnionParameter, DataSetUnionDto.class);
                Long needUnionDataSetId = dataSetUnionDto.getNeedUnionDataSetId();
                Optional<DataByDtIdVo> maxOrderNumDataByDtId = dataProcessInfoMapper.getDataByDtId(needUnionDataSetId)
                        .stream()
                        .max(Comparator.comparing(DataByDtIdVo::getOrderNum));
                if (maxOrderNumDataByDtId.isPresent()) {
                    DataByDtIdVo dataByDtIdVo = maxOrderNumDataByDtId.get();
                    unionTableName = dataByDtIdVo.getTableName();
                    orderNum = dataByDtIdVo.getOrderNum();

                } else {
                    DirectoryEntity directoryEntity = directoryService.getById(needUnionDataSetId);
                    unionTableName = directoryEntity.getTableName();
                    orderNum = 1;
                }
                DataProcessEntity dataProcessById = dataProcessService.getById(groupCollectMergeDto.getDpId());
                if (dataSetUnionDto.getUnionType().equals(Constants.UNION_TYPE_TOP_BOTTOM)) {
                    List<String> unionResultItem = dataSetUnionDto.getUnionResultItem();
                    List<DataSetColumnEntity> list = dataSetColumnService.list(Wrappers
                            .<DataSetColumnEntity>lambdaQuery().eq(DataSetColumnEntity::getDataSetId, groupCollectMergeDto.getDpId()));
                    if (list.size() > 0) {
                        if (list.size() != unionResultItem.size()) {
                            throw new SecurityException("数据集字段数量不一致,请重新选择");
                        }
                    } else {
                        List<DataSetColumnEntity> columnEntities = dataSetColumnService.list(Wrappers
                                .<DataSetColumnEntity>lambdaQuery().eq(DataSetColumnEntity::getDataSetId, dataProcessById.getDtId()));
                        if (columnEntities.size() != unionResultItem.size()) {
                            throw new SecurityException("数据集字段数量不一致,请重新选择");
                        }
                    }

                    String unionSql = unionSql(tableName, unionTableName, unionResultItem).toString();
                    dataProcessInfoEntity.setParameterValues(unionSql);
                } else {

                    List<Long> dataSetIds = new ArrayList<>();
                    dataSetIds.add(dataProcessById.getDtId());
                    dataSetIds.add(dataSetUnionDto.getNeedUnionDataSetId());
                    List<DataSetColumnEntity> dataSetColumnList = dataSetColumnService.list(new LambdaQueryWrapper<DataSetColumnEntity>()
                            .in(DataSetColumnEntity::getDataSetId, dataSetIds));
                    List<String> unionResultItem = dataSetUnionDto.getUnionResultItem();
                    if (dataSetColumnList.size() > 0) {
                        Map<Long, List<DataSetColumnEntity>> dataSetColumnMap = dataSetColumnList.stream().collect(Collectors.groupingBy(DataSetColumnEntity::getDataSetId));
                        //加工数据集字段集合
                        List<DataSetColumnEntity> dataProcessColumn = dataSetColumnMap.get(dataProcessById.getDtId());
                        //合并数据集字段集合
                        List<DataSetColumnEntity> dataSetColumn = dataSetColumnMap.get(dataSetUnionDto.getNeedUnionDataSetId());
                        for (String item : unionResultItem) {
                            DataSetColumnEntity dataSetColumnEntity = dataSetColumn.stream().filter(p -> p.getColumnName().equals(item)).findFirst().orElse(null);
                            if (dataSetColumnEntity != null) {
                                ColumnDto unionColumnDto = new ColumnDto();
                                unionColumnDto.setColumnType(dataSetColumnEntity.getColumnType());
                                unionColumnDto.setColumnComment(dataSetColumnEntity.getColumnComment());
                                unionColumnDto.setColumnName(dataSetColumnEntity.getColumnName());
                                columnDtoList.add(unionColumnDto);
                            }
                        }
                        for (DataSetColumnEntity processColumn : dataProcessColumn) {
                            for (ColumnDto column : columnDtoList) {
                                if (column.getColumnName().equals(processColumn.getColumnName())) {
                                    column.setColumnName(column.getColumnName() + "1");
                                }
                            }
                        }
                        String leftAndRightUnionSql = leftAndRightUnionSql(tableName, unionTableName, orderNum, dataSetUnionDto, dataProcessColumn).toString();
                        dataProcessInfoEntity.setParameterValues(leftAndRightUnionSql);
                    }
                    dataProcessInfoEntity.setColumnJson(JSON.toJSONString(columnDtoList));
                }
                dataProcessInfoEntity.setProcessTable(createTableUtils.createTableName(Constants.PROCESS_DATA, Constants.DATA_SET_UNION_ENGLISH));
                return dataProcessInfoEntity;
            default:
                return null;
        }
    }

    /**
     * 拼接数据赋值列sql
     *
     * @return
     */
    public StringBuilder splicingDataAssignRowSql(String tableName, GroupAssignParameterDto groupAssignParameterDto) {
        String assignBasis = groupAssignParameterDto.getAssignBasis();
        List<DataRangeDto> dataRange = groupAssignParameterDto.getDataRange();
        StringBuilder sql = new StringBuilder("SELECT * , ( CASE ");
        for (DataRangeDto dataRangeDto : dataRange) {
            sql.append(" WHEN ").append(dataRangeDto.getMetricsValueLowerLimit()).append(" <= `")
                    .append(assignBasis).append("` AND `").append(assignBasis).append("` < ")
                    .append(dataRangeDto.getMetricsValueUpperLimit()).append(" THEN '")
                    .append(dataRangeDto.getRangeName()).append("' ");
        }
        return sql.append(" ELSE '' END ) AS `").append(groupAssignParameterDto.getAssignRowName())
                .append("` FROM ").append(tableName);
    }

    /**
     * 拼接数据汇总ql
     *
     * @return
     */
    public StringBuilder splicingDataCollectSql(String tableName, DataCollectDto dataCollectDto) {
        StringBuilder t_str = new StringBuilder();
        t_str.append(dataCollectDto.getChooseCollectMethod()).append("_T");
        StringBuilder b_str = new StringBuilder();
        b_str.append(dataCollectDto.getChooseCollectMethod()).append("_B");
        StringBuilder sql = new StringBuilder("SELECT ");
        return sql.append(t_str).append(".* ,").append(b_str).append(".").append(dataCollectDto.getChooseCollectMethod())
                .append("_DATA AS `").append(dataCollectDto.getCollectField()).append("` FROM ")
                .append(tableName).append(" AS ").append(t_str).append(" INNER JOIN ( SELECT `")
                .append(dataCollectDto.getChooseGroupField()).append("`, ROUND(").append(dataCollectDto.getChooseCollectMethod())
                .append("( `").append(dataCollectDto.getChooseCollectField()).append("` ), 2 ) AS ")
                .append(dataCollectDto.getChooseCollectMethod()).append("_DATA FROM ").append(tableName)
                .append(" GROUP BY `").append(dataCollectDto.getChooseGroupField()).append("` ) AS ")
                .append(b_str).append(" ON ").append(t_str).append(".`").append(dataCollectDto.getChooseGroupField())
                .append("` = ").append(b_str).append(".`").append(dataCollectDto.getChooseGroupField()).append("`");

    }

    /**
     * 左右合并
     *
     * @param primaryTable
     * @param relevanceTable
     * @param orderNum
     * @param dataSetUnionDto
     * @return
     */
    public StringBuilder leftAndRightUnionSql(String primaryTable, String relevanceTable, Integer orderNum, DataSetUnionDto dataSetUnionDto, List<DataSetColumnEntity> dataProcessColumn) {
        StringBuilder sql = new StringBuilder("SELECT ");
        //合并字段
        List<String> unionResultItem = dataSetUnionDto.getUnionResultItem();
        StringBuilder unionSb = new StringBuilder();
        for (String item : unionResultItem) {
            DataSetColumnEntity dataSetColumn = dataProcessColumn.stream().filter(p -> p.getColumnName().equals(item)).findFirst().orElse(null);
            if (dataSetColumn != null) {
                unionSb = unionSb.append("R").append(orderNum).append(".`")
                        .append(item).append("` as ").append(item).append("1 ,");
            } else {
                unionSb = unionSb.append("R").append(orderNum).append(".`")
                        .append(item).append("` ,");
            }
        }
        unionSb.deleteCharAt(unionSb.length() - 1);
        //合并条件
        StringBuilder condition = new StringBuilder("1=1");
        for (UnionBasisDto unionBasi : dataSetUnionDto.getUnionBasis()) {
            condition.append(" AND ").append("L").append(orderNum).append(".`").append(unionBasi.getCurrentBasis())
                    .append("` = ").append("R").append(orderNum).append(".`").append(unionBasi.getUnionTableBasis()).append("`");
        }

        return sql.append(" L").append(orderNum).append(".* ,").append(unionSb).append(" FROM ")
                .append(primaryTable).append(" AS L").append(orderNum)
                .append(dataSetUnionDto.getUnionType()
                        .equals(Constants.UNION_TYPE_LEFT) ? " LEFT JOIN " : " RIGHT JOIN ")
                .append(relevanceTable).append(" AS R").append(orderNum)
                .append(" ON ").append(condition);

    }

    /**
     * 上下合并
     *
     * @param tableName
     * @param relevanceTable
     * @return
     */
    public StringBuilder unionSql(String tableName, String relevanceTable, List<String> unionResultItem) {
        StringBuilder stringBuilder = new StringBuilder();
        String str = unionResultItem.stream()
                .map(account -> "`" + account + "`")
                .collect(Collectors.joining(", "));
        return stringBuilder.append("( SELECT * FROM ").append(tableName).append(" )").append(" UNION ALL ")
                .append("( SELECT ").append(str).append(" FROM ").append(relevanceTable).append(" )");
    }

}
